<?php
/**
 * Author: zsx
 * Date: 2022/9/14
 * Time: 12:17
 * 每个月1号 晚上00:00分执行一次
 * 复制一份仓库表数据到
 */

function pr($data = null) {
  echo '<pre>';
  print_r($data);
  echo '</pre>';
}

function spr($data = null) {
  echo '<pre style="margin:200px;border:3px dotted #666;color:red;padding:168px;">';
  print_r($data);
  echo '</pre>';
}

// 链接数据
$conn = mysqli_connect('localhost', 'root', '123456', 'guanji');
$date = date('Y-m-d',strtotime('-1 day'));
$date = '2022-09-28';
$nowDate = date('Y-m-d');
if ($conn) {
  // 获取所有加工的原料数据
  $sql = "SELECT
	ho.*, g.price_in AS default_contrast_cost
FROM
	house_out AS ho
LEFT JOIN goods AS g ON g.id = ho.goods_id
WHERE
	ho.process = 0
AND ho.status = 1
AND ho.type = 1
AND ho.sale_time LIKE '{$date}%'";
  $result = mysqli_query($conn, $sql);
  // 原料总成本
  $totalMaterialCost = 0;
  while($row = mysqli_fetch_assoc($result))
  {
    $totalMaterialCost += $row['count'] * $row['price'];
  }
  // 成品生成数据
  $products = [];
  $sql1 = "SELECT
	hi.*, g.price_in AS default_contrast_cost, g.conversion
FROM
	house_in AS hi
LEFT JOIN goods AS g ON g.id = hi.goods_id
WHERE
	hi.process = 1
AND hi.status = 1
AND hi.insert_time LIKE '{$date}%'";
  $result1 = mysqli_query($conn, $sql1);
  // 参考总成本
  $totalDefaultCost = 0;
  while($row1 = mysqli_fetch_assoc($result1))
  {
    $products[] = $row1;
    $totalDefaultCost += $row1['count_in'] * $row1['default_contrast_cost'];
  }
  foreach ($products as $value) {
    $updateSql = '';
    // 实际成本 = ((参考成本 * 数量)/参考总成本 * 实际总成本) / 数量
    // 优化得：参考成本 / 参考总成本 * 实际总成本
    $contrastCost = $totalDefaultCost ? round($value['default_contrast_cost'] / $totalDefaultCost * $totalMaterialCost) : 0;
    // 总成本：
    $total = round($contrastCost * $value['count_in'], 2);
    $count = $value['count_in'] * $value['conversion'];
    $price = $count ? round($total / $count, 2) : 0;
    $updateSql = "UPDATE `house_in` SET `price_in`='{$contrastCost}', `price`='{$price}', `total_amount`='{$total}', `count`='{$count}', `last_update_time`='{$nowDate}' WHERE (`id`='{$value['id']}');";
//    mysqli_query($conn, $updateSql);
  }
  print_r($products);die;

//  mysqli_close($conn);
}